<?php

namespace Kcdns\Service\Util;

require_once 'PHPExcel/PHPExcel.php';

class Excle
{

    public static function readXsl($file, $del_first_line = false)
    {
//        throw new \Exception('导入功能维护中...请联系管理员');

        $PHPReader = new \PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($file)) {
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($file)) {
                throw new \Exception('read xls file fail');
            }
        }

        $reader = $PHPReader->load($file);
        $cur = $reader->getSheet(0); // 读取第一个表
        $line = $cur->getHighestRow(); // 获得最大总行数
        $end = $cur->getHighestColumn(); // 获得最大的列数
        $result = array();
        for ($row = 1; $row <= $line; $row++) {//从第二行开始
            for ($column = 'A'; $column <= $end; $column++) {
                $val = $cur->getCellByColumnAndRow(ord($column) - 65, $row)->getValue();
//                $val = gmdate("Y-m-d", ($val - 25569) * 24 * 60 * 60);//日期格式转换
                $result[$row - 1][] = strval($val);
            }

            if (!array_filter($result[$row - 1])) {
                unset($result[$row - 1]);
            }
        }

        if ($del_first_line) {
            unset($result[0]);
        }

        return $result;
    }

    public static function writeXls($data, $filename)
    {
        $letter = range('A', 'Z');
        $excel = new \PHPExcel();
        $excel->setActiveSheetIndex(0);
        $objActSheet = $excel->getActiveSheet();

        $row = 1;
        foreach ($data as $arr) {
            $column = 0;
            $objActSheet->getStyle($row)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
            foreach ($arr as $key => $val) {
                $val = is_array($val) ? array_values($val)[0] : $val;
                $objActSheet->setCellValueExplicit("$letter[$column]$row", $val, \PHPExcel_Cell_DataType::TYPE_STRING);
                $column++;
            }
            $row++;
        }

        $file = RUNTIME_PATH . '/' . ($filename ?: date('ymdHis')) . '.xlsx';
        $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $objWriter->save($file);
        return $file;
    }
}